package com.sit.estatemanagementserver.dao;

import com.sit.estatemanagementserver.enity.Repair;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
public class RepairDao {
    @Autowired
    DatabaseManager db;

    // 重构: 适配新的 Repair 构造函数，增加 imageId
    private Repair resultSetToRepair(ResultSet rs) throws SQLException {
        return new Repair(
                rs.getString(DatabaseContract.RepairEntry.COLUMN_WORK_ORDER_ID),
                rs.getString(DatabaseContract.RepairEntry.COLUMN_MESSAGE),
                rs.getString(DatabaseContract.RepairEntry.COLUMN_IMAGE_ID_FK),
                rs.getInt(DatabaseContract.RepairEntry.COLUMN_IS_HANDLED) == 1,
                rs.getString(DatabaseContract.RepairEntry.COLUMN_ROOM_NUMBER_FK)
        );
    }

    public boolean addRepair(Repair repair) {
        // 重构: SQL语句增加 image_id 列
        String sql = "INSERT INTO " + DatabaseContract.RepairEntry.TABLE_NAME + " ("+
                DatabaseContract.RepairEntry.COLUMN_WORK_ORDER_ID + ", " +
                DatabaseContract.RepairEntry.COLUMN_MESSAGE + ", " +
                DatabaseContract.RepairEntry.COLUMN_IMAGE_ID_FK + ", " +
                DatabaseContract.RepairEntry.COLUMN_IS_HANDLED + ", " +
                DatabaseContract.RepairEntry.COLUMN_ROOM_NUMBER_FK + ") VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, repair.getWorkOrderId());
            pstmt.setString(2, repair.getMessage());
            pstmt.setString(3, repair.getImageId()); // 新增字段
            pstmt.setInt(4, repair.isHandled() ? 1 : 0);
            pstmt.setString(5, repair.getRoomNumber());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error adding repair '" + repair.getWorkOrderId() + "': " + e.getMessage());
            return false;
        }
    }

    public Repair getRepair(String workOrderId) {
        String sql = "SELECT * FROM " + DatabaseContract.RepairEntry.TABLE_NAME + " WHERE " +
                DatabaseContract.RepairEntry.COLUMN_WORK_ORDER_ID + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, workOrderId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return resultSetToRepair(rs);
                }
            }
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error getting repair '" + workOrderId + "': " + e.getMessage());
        }
        return null;
    }

    public List<Repair> getAllRepairs() {
        List<Repair> repairs = new ArrayList<>();
        String sql = "SELECT * FROM " + DatabaseContract.RepairEntry.TABLE_NAME;
        try (Connection conn = db.connect();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                repairs.add(resultSetToRepair(rs));
            }
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error getting all repairs: " + e.getMessage());
        }
        return repairs;
    }

    public List<Repair> getRepairsByRoom(String roomNumber) {
        List<Repair> repairs = new ArrayList<>();
        String sql = "SELECT * FROM " + DatabaseContract.RepairEntry.TABLE_NAME + " WHERE " +
                DatabaseContract.RepairEntry.COLUMN_ROOM_NUMBER_FK + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, roomNumber);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    repairs.add(resultSetToRepair(rs));
                }
            }
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error getting repairs for room '" + roomNumber + "': " + e.getMessage());
        }
        return repairs;
    }

    public boolean updateRepair(Repair repair) {
        // 重构: SQL语句增加 image_id 列
        String sql = "UPDATE " + DatabaseContract.RepairEntry.TABLE_NAME + " SET " +
                DatabaseContract.RepairEntry.COLUMN_MESSAGE + " = ?, " +
                DatabaseContract.RepairEntry.COLUMN_IMAGE_ID_FK + " = ?, " +
                DatabaseContract.RepairEntry.COLUMN_IS_HANDLED + " = ?, " +
                DatabaseContract.RepairEntry.COLUMN_ROOM_NUMBER_FK + " = ? WHERE " +
                DatabaseContract.RepairEntry.COLUMN_WORK_ORDER_ID + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, repair.getMessage());
            pstmt.setString(2, repair.getImageId()); // 新增字段
            pstmt.setInt(3, repair.isHandled() ? 1 : 0);
            pstmt.setString(4, repair.getRoomNumber());
            pstmt.setString(5, repair.getWorkOrderId());
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error updating repair '" + repair.getWorkOrderId() + "': " + e.getMessage());
            return false;
        }
    }

    public boolean deleteRepair(String workOrderId) {
        String sql = "DELETE FROM " + DatabaseContract.RepairEntry.TABLE_NAME + " WHERE " +
                DatabaseContract.RepairEntry.COLUMN_WORK_ORDER_ID + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, workOrderId);
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error deleting repair '" + workOrderId + "': " + e.getMessage());
            return false;
        }
    }
}